#!/usr/bin/env python
# -*- coding: utf-8 -*-
import sys
try:
    import pymysql as MySQLdb
except:
    import MySQLdb
import logging
import hashlib
import codecs
try:
    import pickle as Pickle
except:
    import cPickle as Pickle
try:
    if sys.version_info.major<3:
        from urllib import quote, unquote
    else:
        from urllib.parse import quote, unquote
        unicode = str
except:
    pass


xorStr = lambda ss,cc: ''.join(chr(ord(s)^ord(cc)) for s in ss)


class Mysql:
    def __init__(self, dbconfig, _autoCount=0):
        self._mysql_config = dbconfig
        self._auto_count = int(_autoCount)
        self._execute_count = self.auto_count(0)
    
    def encrypt_auth(self, data,key=None):
        enStr = data
        for cc in key:
            enStr = xorStr(enStr,cc)
        if isinstance(enStr, str):
            enStr = enStr.encode("utf-8")
        return quote(base64.b64encode(enStr))

    def auto_count(self, start):
        c = [start]
        def _count():
            c[0] += 1
            return c[0]
        return _count

    def calMd5(self, data):
        m = hashlib.md5()
        try:
            data = data.encode('utf8')
        except:
            pass
        m.update(data)
        return m.hexdigest()

    def _get_file_fd(self, filename, append=False):
        if not hasattr(self, '_file_dict'):
            self._file_dict = {}
        if filename not in self._file_dict:
            m = 'a' if append else 'w'
            fd = codecs.open(filename, mode=m, encoding='utf-8')
            self._file_dict[filename] = fd
        return self._file_dict[filename]

    def _save_to_pkl(self, filename, sql):
        fd = self._get_file_fd(filename)
        Pickle.dump(sql, fd)

    def _save_to_file(self, filename, sql, append=False):
        fd = self._get_file_fd(filename, append)
        r = sql.strip().strip(';')
        fd.write(r+';\n')
        fd.flush()

    def _file_close(self, filename=None):
        if not hasattr(self, '_file_dict'):
            return
        if not filename:
            for fname in self._file_dict:
                fd = self._file_dict[fname]
                fd.close()
            self._file_dict = {}
            return
        if filename not in self._file_dict:
            return
        fd = self._file_dict.pop(filename)
        fd.close()

    '''
    def __del__(self):
        self._file_close()
        self._mysql_close()
        
    '''

    def _mysql_close(self):
        if hasattr(self, '_mysql_con'):
            self._mysql_con.close()

    def _mysql_get_con(self):
        if not hasattr(self, "_mysql_con"):
            self._mysql_con = MySQLdb.connect(**self._mysql_config)
            self._mysql_con.autocommit(False)
        return self._mysql_con

    def _mysql_get_cur(self, dictCursor=True):
        if not hasattr(self, "_mysql_cur"):
            con = self._mysql_get_con()
            if dictCursor:
                self._mysql_cur = con.cursor(MySQLdb.cursors.DictCursor)
            else:
                self._mysql_cur = con.cursor()
            self._mysql_cur.execute('set names utf8')
            self._mysql_cur.execute('SET CHARACTER SET utf8')
            self._mysql_cur.execute('SET character_set_connection=utf8')
        return self._mysql_cur

    def select_one(self, sql):
        cur = self._mysql_get_cur()
        cur.execute(sql)
        return cur.fetchone()

    def select(self, sql):
        cur = self._mysql_get_cur()
        cur.execute(sql)
        return cur.fetchall()

    def execute(self, sql, toFile=None, _commit=True, _getlastid=False):
        cur = self._mysql_get_cur()
        try:
            cur.execute(sql)
        except Exception as e:
            print(e)
            print(sql)
            exit(0)
        if _commit:
            self.commit()
        elif self._auto_count > 0:
            _c = self._execute_count()
            if _c % self._auto_count == 0:
                self.commit()
                print('==>>count commit (%d)-->>%s' % (_c, sql))
        if toFile:
            self._save_to_file(toFile, sql)
        if _getlastid:
            return cur.lastrowid

    def commit(self):
        if hasattr(self, "_mysql_con"):
            self._mysql_con.commit()

    def construct_insert_sql(self, table, data, db=None):
        fields = '`' + '`,`'.join(data.keys()) + '`'
        values = []
        for v in data.values():
            if v == 0:
                v = '0'
            elif not v:
                v = ''
            if not isinstance(v, str) and not isinstance(v, unicode):
                values.append(v.__str__())
            elif v == "now()":
                values.append(v)
            else:
                values.append("'%s'" % v.replace("'", " ").replace("\\", "\\\\"))
        if db:
            sql = 'INSERT INTO `%s`.`%s` (%s) VALUES (%s)' % (db, table, fields, ",".join(values))
        else:
            sql = 'INSERT INTO `%s` (%s) VALUES (%s)' % (table, fields, ",".join(values))
        return sql

    def create_database(self, dbname, drop=False):
        if drop:
            sql = 'drop database if exists `%s`' % dbname
            self._mysql_execute(sql)
        sql = 'create database if not exists `%s`' % dbname
        self.execute(sql)

    def construct_update_sql(self, table, data, where=None):
        sql = 'update `%s` set ' % table
        vals = []
        for k in data:
            v = data[k]
            if not v:
                v = "''"
            if type(v) == int or type(v) == long:
                v = "'{}'".format(v)
            elif v == "now()":
                pass
            else:
                v = "'%s'" % v.replace("'", " ").replace("\\", "\\\\")
            vals.append("`%s`=%s" % (k, v))
        sql += ','.join(vals)
        if where:
            if isinstance(where, dict):
                sql += ' where ' + ','.join(["`%s`='%s'" % (k, where[k]) for k in where])
            else:
                sql += ' where ' + where
        return sql
    
    def limit_select(self, in_sql, start, step, screen=True):
        while True:
            # sql = 'select `{}` from `{}` {} limit {},{}'.format(f, table, w, start, step)
            sql = in_sql + ' limit {},{}'.format(start, step)
            if screen:
                print('==>>current step-->>', sql)
            dataList = self.select(sql)
            if not dataList:
                break
            start += step
            for data in dataList:
                yield data
            # break
        raise StopIteration()
    
    def limit_select_by_id(self, table, fields, step, min_id=None, where=None, autoId='id',screen=True):
        if not min_id:
            sql = 'select min(`{}`) as min_id from `{}`'.format(autoId, table)
            min_id = self.select_one(sql)
            if not min_id:
                raise StopIteration()
            try:
                min_id = min_id['min_id'] - 1
            except:
                raise StopIteration()
        fs = '`' + '`,`'.join(fields) + '`' if fields else '*'
        if where:
            wh = ' and {} '.format(where)
        else:
            wh = ''
        while True:
            if fields and autoId not in fields:
                sql = '''select `{}`,{} from `{}` where `{}`>{} {} order by `{}` asc limit {}'''.format(autoId, fs, table, autoId, min_id, wh, autoId, step)
            else:
                sql = '''select {} from `{}` where `{}`>{} {} order by `{}` asc limit {}'''.format(fs, table, autoId, min_id, wh, autoId, step)
            if screen:
                print('==>>current step-->>', sql)
            dataList = self.select(sql)
            if not dataList:
                break
            min_id += step
            for data in dataList:
                last_id = data[autoId]
                yield data
            if last_id > min_id:
                min_id = last_id
            # break
        raise StopIteration()
